package database_layer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import processing_layer.Course;
import processing_layer.CourseSession;
import processing_layer.User;

/**
 * This class handles all the "data fetching" queries to the DB.
 * Since each function holds only one query, we decided there's no need
 * to use commits and rollback here, additionally to the fact that none of these
 * functions alters the tables.
 * 
 * The isolation level used is READ_COMMITTED, again, since there's only one query
 * and it's enough that this level acquires read locks and gets all the committed values
 * before the transaction start.
 *
 */
public final class QueryDispatcher
{
	public static User getUserDetails(String username)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);

			statement = con.prepareStatement("SELECT * FROM " + Common.usersTable + " WHERE " +
		                                     Common.usernameField + "=?");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			if (result.next())
			{
				User userDetails = new User(result.getString(Common.usernameField),
											result.getString(Common.passwordField),
											result.getString(Common.firstNameField),
											result.getString(Common.lastNameField),
											result.getString(Common.phoneField),
											result.getString(Common.addressField),
											result.getBoolean(Common.adminField),
											result.getBlob(Common.photoField));
				return userDetails;
			}
			else
			{
				return new User(null,null,null,null,null,null,false,null);
			}
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}
	}
	
	public static Course getCourseDetails(String courseName)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " WHERE " +
					                         Common.courseNameField + "=?");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			if (result.next())
			{
				Course courseDetails = new Course(result.getString(Common.courseNameField),
											      result.getString(Common.descriptionField),
											      result.getFloat(Common.creditPointsField),
											      result.getInt(Common.capacityField),
											      result.getInt(Common.alreadyRegisteredField),
											      result.getInt(Common.groupNumberField),
											      result.getString(Common.courseCreatorField));
				return courseDetails;
			}
			
			else
			{
				return new Course(null, null, 0, 0, 0, 0, null);
			}
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}		
	}
	
	public static ArrayList<CourseSession> getCourseSessions(String courseName)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT * FROM " + Common.sessionsTable + " WHERE " +
											 Common.courseNameField + "=?");
			statement.setString(1, courseName);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			ArrayList<CourseSession> sessions = new ArrayList<CourseSession>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				CourseSession session = new CourseSession(result.getString(Common.courseNameField),
											      		  result.getInt(Common.dayField),
														  result.getInt(Common.startingTimeField),
											              result.getInt(Common.endingTimeField), 
											              result.getInt(Common.durationField),
											              result.getInt(Common.groupNumberField));
				sessions.add(session);
			}
			
			return sessions;
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}		
	}
	
	public static ArrayList<Course> getAllCourses()
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			ArrayList<Course> courses = new ArrayList<Course>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				Course courseDetails = new Course(result.getString(Common.courseNameField),
											      result.getString(Common.descriptionField),
											      result.getFloat(Common.creditPointsField),
											      result.getInt(Common.capacityField),
											      result.getInt(Common.alreadyRegisteredField),
											      result.getInt(Common.groupNumberField),
											      result.getString(Common.courseCreatorField));
				courses.add(courseDetails);
			}
			
			return courses;
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}		
	}
	
	public static ArrayList<User> getAllUsers()
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT * FROM " + Common.usersTable);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			ArrayList<User> users = new ArrayList<User>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				User userDetails = new User(result.getString(Common.usernameField),
											result.getString(Common.passwordField),
											result.getString(Common.firstNameField),
											result.getString(Common.lastNameField),
											result.getString(Common.phoneField),
											result.getString(Common.addressField),
											result.getBoolean(Common.adminField),
											result.getBlob(Common.photoField));
				users.add(userDetails);
			}
			
			return users;
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}		
	}
	
	public static ArrayList<Course> getAllAvailableCourses(String username)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			/* Pre-condition strings */
			
			/* One ? [AND] */
			String notRegisteredTo = "NOT EXISTS (SELECT * FROM " + Common.registeredTable + " AS r WHERE " +
								     "r." + Common.courseNameField + "=c." + Common.courseNameField + " AND r." + Common.usernameField + "=?)";
			
			/* No ? [AND] */
			String thereIsRoom = "c." + Common.capacityField + " > c." + Common.alreadyRegisteredField;
			
			/* One ? [AND, OR with the one below]*/
			String registeredToGroup = "(EXISTS (SELECT DISTINCT " + Common.groupNumberField + " FROM " + 
			                           Common.registeredTable + " AS r2 INNER JOIN " +
			                           Common.coursesTable + " AS c2 ON r2." + Common.courseNameField + "=" +
			                           "c2." + Common.courseNameField + " WHERE c." + Common.groupNumberField + 
			                           "=c2." + Common.groupNumberField + " AND r2." + Common.usernameField + "=?))";
			
			/* One ? [AND, OR with the one above*/
			String notMaxGroupNumber = "(" + Common.MAX_GROUPS_FOR_USER + " > (SELECT COUNT(DISTINCT " + Common.groupNumberField + ") FROM " + 
			                           Common.registeredTable + " AS r3 INNER JOIN " +
                                       Common.coursesTable + " AS c3 ON r3." + Common.courseNameField + "=" +
                                       "c3." + Common.courseNameField + " WHERE r3." + Common.usernameField + "=?))";
			/* One ? [AND] */
			String noOverlap = "NOT EXISTS (SELECT * FROM " + Common.sessionsTable + " AS s WHERE s." + Common.courseNameField +
							   "=c." + Common.courseNameField + " AND EXISTS " +
							   "(SELECT * FROM " + Common.coursesTable + " as c4 INNER JOIN " + Common.sessionsTable +
							   " AS s2 ON c4." + Common.courseNameField + "=s2." + Common.courseNameField + " INNER JOIN " +
							   Common.registeredTable + " AS r4 ON c4." + Common.courseNameField + "=r4." + Common.courseNameField +
							   " WHERE r4." + Common.usernameField + "=? AND s." + Common.dayField + "=s2." + Common.dayField + " AND " +
							   "((s." + Common.startingTimeField + "> s2." + Common.startingTimeField + " AND s." + Common.startingTimeField + 
							   "< s2." + Common.endingTimeField + ") OR " +
							   "(s." + Common.endingTimeField + "> s2." + Common.startingTimeField + " AND s." + Common.endingTimeField + 
								"< s2." + Common.endingTimeField + ") OR " + 
								"(s2." + Common.startingTimeField + "> s." + Common.startingTimeField + " AND s2." + Common.startingTimeField + 
								"< s." + Common.endingTimeField + ") OR " + 
								"(s2." + Common.endingTimeField + "> s." + Common.startingTimeField + " AND s2." + Common.endingTimeField + 
								"< s." + Common.endingTimeField + "))))";
							   
							   
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " AS c WHERE " +
											 notRegisteredTo + " AND " + thereIsRoom + " AND " + 
											 noOverlap + " AND (" + registeredToGroup + " OR " + notMaxGroupNumber + ")");
			statement.setString(1, username);
			statement.setString(2, username);
			statement.setString(3, username);
			statement.setString(4, username);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			ArrayList<Course> courses = new ArrayList<Course>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				Course courseDetails = new Course(result.getString(Common.courseNameField),
											      result.getString(Common.descriptionField),
											      result.getFloat(Common.creditPointsField),
											      result.getInt(Common.capacityField),
											      result.getInt(Common.alreadyRegisteredField),
											      result.getInt(Common.groupNumberField),
											      result.getString(Common.courseCreatorField));
				courses.add(courseDetails);
			}
			
			return courses;
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}			
	}
	
	public static ArrayList<Course> getRegisteredCourses(String userName)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " AS c WHERE " +
											 "EXISTS (SELECT * FROM " + Common.registeredTable + " AS r INNER JOIN " +
											 Common.sessionsTable + " AS s ON r." + Common.courseNameField + "=s." + Common.courseNameField +
											 " WHERE r." + Common.courseNameField + "=c." + Common.courseNameField + 
											 " AND r." + Common.usernameField + "=?)");
			statement.setString(1, userName);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			ArrayList<Course> courses = new ArrayList<Course>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				Course courseDetails = new Course(result.getString(Common.courseNameField),
											      result.getString(Common.descriptionField),
											      result.getFloat(Common.creditPointsField),
											      result.getInt(Common.capacityField),
											      result.getInt(Common.alreadyRegisteredField),
											      result.getInt(Common.groupNumberField),
											      result.getString(Common.courseCreatorField));
				courses.add(courseDetails);
			}
			
			return courses;
		}
		catch (Exception e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}		
	}
	
	public static ArrayList<CourseSession> getRegisteredSessions(String userName)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);;
			statement = con.prepareStatement("SELECT * FROM " + Common.sessionsTable + " AS s WHERE " +
											 "EXISTS(SELECT * FROM " + Common.registeredTable + " As r WHERE " +
											 "r." + Common.courseNameField + "=s." + Common.courseNameField + " AND " +
											 "r." + Common.usernameField + "=?)");
			statement.setString(1, userName);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			ArrayList<CourseSession> sessions = new ArrayList<CourseSession>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				CourseSession session = new CourseSession(result.getString(Common.courseNameField),
											      		  result.getInt(Common.dayField),
														  result.getInt(Common.startingTimeField),
											              result.getInt(Common.endingTimeField), 
											              result.getInt(Common.durationField),
											              result.getInt(Common.groupNumberField));
				sessions.add(session);
			}
			
			return sessions;
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}		
	}
	
	public static ArrayList<Course> getAllGeneratedCourses(String username)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " WHERE " +
		                                     Common.courseCreatorField + " = ?");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			ArrayList<Course> courses = new ArrayList<Course>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				Course courseDetails = new Course(result.getString(Common.courseNameField),
											      result.getString(Common.descriptionField),
											      result.getFloat(Common.creditPointsField),
											      result.getInt(Common.capacityField),
											      result.getInt(Common.alreadyRegisteredField),
											      result.getInt(Common.groupNumberField),
											      result.getString(Common.courseCreatorField));
				courses.add(courseDetails);
			}
			
			return courses;
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}		
	}
	
	public static HashMap<String,Course> findCoursesByParameter(String searchedField, String parameter)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT * FROM " + Common.coursesTable + " WHERE " +
											 searchedField + " LIKE ?");
			String parameterWithMatchAny = '%' + parameter + '%';
			statement.setString(1, parameterWithMatchAny);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
			
			HashMap<String,Course> courses = new HashMap<String,Course>();
			
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				Course courseDetails = new Course(result.getString(Common.courseNameField),
											      result.getString(Common.descriptionField),
											      result.getFloat(Common.creditPointsField),
											      result.getInt(Common.capacityField),
											      result.getInt(Common.alreadyRegisteredField),
											      result.getInt(Common.groupNumberField),
											      result.getString(Common.courseCreatorField));
				
				courses.put(courseDetails.name, courseDetails);
			}	
			return courses;
		}
		catch (SQLException e)
		{
			return null;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}			
	}
	
	public static float getUserCreditPoints(String username)
	{
		Connection con = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try
		{
			con = DatabaseManager.getConnection(Connection.TRANSACTION_READ_COMMITTED, true);
			statement = con.prepareStatement("SELECT COALESCE(SUM(" + Common.creditPointsField + "),0) AS total_credit FROM " + Common.coursesTable + " AS c," +
											 Common.registeredTable + " AS r WHERE r." + Common.courseNameField + "=c." + Common.courseNameField +
											 " AND r." + Common.usernameField + "=?");
			statement.setString(1, username);
			DatabaseManager.testPrint(statement.toString());
			result = statement.executeQuery();
					
			/* If next indicates true, then we have an entry, otherwise, there are no entries! */
			while (result.next())
			{
				return result.getFloat("total_credit");
			}	
			return 0;
		}
		catch (SQLException e)
		{
			return -1;
		}
		finally
		{
			DatabaseManager.closeQuery(con, statement, result);
		}			
	}
}
